package com.elasticsearch.dbuitl;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 用于JDBC操作数据库的公共类
 * 
 * @author jackKang<br/>
 *         E-mail: vipbooks@163.com
 * @version 2009-2-14
 */
public class CommonSql {
	/** 数据库连接对象 */
	private Connection conn;
	/** 数据库操作对象 */
	private PreparedStatement ps;
	/** 返回的数据结果集对象 */
	private ResultSet rs;

	/**
	 * 测试数据库连接是否成功
	 * 
	 * @param args
	 */

	public static void main(String[] args) {
		CommonSql c = new CommonSql();
		if (c.openConn())
			System.out.println("数据库连接成功！");
		else
			System.out.println("数据库连接失败！");

		c.closeAll();
	}

	/**
	 * 打开数据库连接并创建数据库连接对象
	 * 
	 * @return boolean true:连接成功，false:连接失败
	 */
	public boolean openConn() {
		Boolean isPassed = false;
		String driver = ReadConfigation.getConfigItem("jdbc.driverClassName");
		String url = ReadConfigation.getConfigItem("jdbc.url");
		String user = ReadConfigation.getConfigItem("jdbc.username");
		String pwd = ReadConfigation.getConfigItem("jdbc.password");

		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pwd);
			isPassed = true;
		} catch (ClassNotFoundException e) {
			closeAll();
			e.printStackTrace();
			System.out.println("数据库连接失败！");
		} catch (Exception e) {
			closeAll();
			e.printStackTrace();
			System.out.println("数据库连接失败！");
		}

		return isPassed;
	}

	/**
	 * 执行数据库的新增和修改语句，只操作一张表
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @return boolean true:执行成功，false:执行失败
	 */
	public boolean execUpdate(String sql) {
		boolean isPassed = false;
		// 判断连接数据库是否成功
		if (openConn()) {
			try {
				conn.setAutoCommit(false);
				ps = conn.prepareStatement(sql);
				ps.executeUpdate();
				conn.commit();
				isPassed = true;
			} catch (SQLException e) {
				System.out.println("SQL:" + sql);
				e.printStackTrace();
			} finally {
				closeAll();
			}
		} else {
			closeAll();
			System.out.println("SQL:" + sql);
			System.out.println("数据库连接失败！");
		}

		return isPassed;
	}

	/**
	 * 执行数据库的新增和修改语句，同时操作多张表
	 * 
	 * @param sql
	 *            要执行的SQL语句的字符串数组
	 * @return boolean true:执行成功，false:执行失败
	 */
	public boolean execUpdate(String[] sql) {
		boolean isPassed = false;
		// 判断连接数据库是否成功
		if (openConn()) {
			try {
				conn.setAutoCommit(false);
				for (int i = 0; i < sql.length; i++) {
					ps = conn.prepareStatement(sql[i]);
					ps.executeUpdate();
				}
				conn.commit();
				isPassed = true;
			} catch (SQLException e) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				for (int i = 0; i < sql.length; i++) {
					System.out.println("SQL:" + sql[i]);
				}
				e.printStackTrace();
			} finally {
				closeAll();
			}
		} else {
			closeAll();
			for (int i = 0; i < sql.length; i++) {
				System.out.println(sql[i]);
			}
			System.out.println("数据库连接失败！");
		}

		return isPassed;
	}

	/**
	 * 执行数据库的新增和修改语句，同时操作多张表
	 * 
	 * @param sql
	 *            要执行的SQL语句的集合
	 * @return boolean true:执行成功，false:执行失败
	 */
	public boolean execUpdate(List<String> sql) {
		boolean isPassed = false;
		// 判断连接数据库是否成功
		if (openConn()) {
			try {
				conn.setAutoCommit(false);
				for (int i = 0; i < sql.size(); i++) {
					ps = conn.prepareStatement(sql.get(i));
					ps.executeUpdate();
				}
				conn.commit();
				isPassed = true;
			} catch (SQLException e) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
				for (int i = 0; i < sql.size(); i++) {
					System.out.println("SQL:" + sql.get(i));
				}
				e.printStackTrace();
			} finally {
				closeAll();
			}
		} else {
			closeAll();
			for (int i = 0; i < sql.size(); i++) {
				System.out.println(sql.get(i));
			}
			System.out.println("数据库连接失败！");
		}

		return isPassed;
	}

	/**
	 * 执行数据库查询操作
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @return ResultSet 返回查询的结果集对象
	 */
	public ResultSet execQuery(String sql) {
		rs = null;
		// 判断连接数据库是否成功
		if (openConn()) {
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
			} catch (SQLException e) {
				closeAll();
				System.out.println("SQL:" + sql);
				e.printStackTrace();
			}
		} else {
			closeAll();
			System.out.println("SQL:" + sql);
			System.out.println("数据库连接失败！");
		}

		return rs;
	}

	/**
	 * 关闭所有数据库连接对象
	 */
	public void closeAll() {
		if (conn != null) {
			try {
				conn.close();
				conn = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
				ps = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
